(Sun Microsystems, Inc.)(1997).iso/products/Cobb/ordlogo.gif)
Garrett J. Suhm, Tactics, Inc.
This is the first article in a two-part series on network performance.
Your organization has just purchased top-of-the-line Pentium workstations, loaded with memory. Your server has more than enough power, but your customers constantly complain that all your applications are slow. What's going on?
The problem may well be poor use of your network resources. Many companies have stretched their networks to the limit due to design problems and unforeseen growth. Compounding the problem are network-unfriendly file databases such as Microsoft's Access and FoxPro and Borland's dBASE. While there isn't much you can do about other people's use or misuse of the available network bandwidth, you can use certain techniques to minimize your applications' appetite for resources. This is even more important if your applications are used over dial-up lines where speeds are usually no faster than 28.8KBPS.
There are many variables in application development, and you'll need to take different approaches depending on the task at hand. We'll look at the following areas:
--Getting the minimal amount of data necessary
--Moving business logic to the server
--Minimizing the number of requests for data to the server
The following techniques will help you minimize the amount of traffic your applications generate, decreasing response time and possibly improving the performance of everything else that depends on the network.
Oracle Forms 4.5 already does a lot to help minimize network traffic. When you execute a query on a block, only the first group of records of the query is actually retrieved from the database. You'll notice this if you have a scrollbar on a multirow block. Every time you scroll down, the button in the middle moves up as each new group of records is retrieved. The number of records retrieved is controlled by the Block property Records Fetched, which defaults to the same value as the Records Displayed property. This setup eliminates a lot of network traffic but can be annoying to a user who needs to have all records available.
You can set Records Fetched to a higher value, which will also help eliminate the strange scrollbar effect. But remember, a higher setting will allow more data to come across the network, regardless of whether the user needs to see it or not.
One data structure in Oracle Forms that doesn't retrieve data as needed is record groups. These groups are used for all lists of values and can also be created at runtime. When you use a select statement to populate a record group, all the data is immediately returned to the form. If your select statement is not restrictive enough, a very large amount of data could be transferred to fill the record group, resulting in unnecessary delays and a large amount of network traffic. For this reason, it's always a good idea to restrict your select statement to the smallest query possible.
You can use PL/SQL procedures to implement plenty of business rules and logic in your forms. You can build them either as part of the form or as stored procedures pre-compiled in the database. Unfortunately, many developers choose to use the PL/SQL procedures in forms because they have easy access to form fields, global variables, and form status variables. These are nice things to have, but you pay a price both in portability and performance. The moment you access a variable that isn't private to the procedure or passed in as a parameter, you tie this procedure to its form. Also, a form-based procedure is then executed on the client, which in most cases is less powerful than the server. Even more important, each select statement within the procedure must travel across the network instead of being handled internally on the server. In most cases, moving the PL/SQL procedure to the server will be your best bet.
When you're dealing with non-traditional data types such as images and OLE objects, consider their implication on network traffic. These objects can become quite large and require a lot of bandwidth. In some cases, it might make sense to retrieve the object only if the user specifically requests it.
If you're still using SQL*Net Version 1, you should really consider upgrading to SQL*Net 2.1. Oracle Corporation has made many improvements in performance with the new version. The decrease in network traffic will help your applications across the board.
A potential drain on network resources is the use of timers for data retrieval. Timers, which you can set up to automatically query the database at predetermined intervals, provide a great way to get almost real-time status information from the database. While this feature is very useful, you'll want to maximize the data retrieval interval and select the minimum amount of data needed for the form.
As you can see, there are a lot of areas within Developer/2000 that affect network performance. Spending a little time in the beginning can pay off with a more responsive application. Next month, we'll explore the use of join views to minimize network traffic.
Garrett Suhm is a software consultant for Tactics, Inc. You can reach him at (404) 248-1226 or send E-mail to gsuhm@GroupZ.net.
[Return to Index for Exploring Oracle Developer/2000 and Designer/2000 - March 1996]
Copyright (c) 1996 The Cobb Group, a division of Ziff-Davis Publishing Company. All rights reserved. Reproduction in whole or in part in any form or medium without express written permission of Ziff-Davis Publishing Company is prohibited. The Cobb Group and The Cobb Group logo are trademarks of Ziff-Davis Publishing Company.